# -*- coding: utf-8 -*-
# @Time    : 2023/10/20 22:07
# @Author  : Alvin
# @File    : DatabaseHandler.py
# @Des     : 数据库处理类

import logging
import multiprocessing
import os
import traceback

import pymysql
from dbutils.pooled_db import PooledDB
from pymysql.cursors import DictCursor

from model.LanShouModel import LanShouModel
from utils import CommonUtil


class ConnectionContext:
    """数据库连接上下文管理器"""

    def __init__(self, pool):
        self.pool = pool
        self.conn = None
        self.cursor = None

    def __enter__(self):
        self.conn = self.pool.connection()
        self.cursor = self.conn.cursor()
        return self.cursor

    def __exit__(self, exc_type, exc_val, exc_tb):
        if exc_type is not None:
            self.conn.rollback()
        else:
            self.conn.commit()
        self.cursor.close()
        self.conn.close()


class DatabaseHandler(object):
    _instance = None
    _lock = multiprocessing.Lock()

    def __new__(cls, *args, **kwargs):
        with cls._lock:
            if not cls._instance:
                cls._instance = super().__new__(cls)
                cls._init_pool()
            return cls._instance

    @classmethod
    def _init_pool(cls):
        """初始化连接池（每个进程独立）"""
        if os.name == 'nt':
            host = '175.24.32.188'
        else:
            host = 'localhost'
        pool_config = {
            "host": host,
            "user": "root",
            "password": "dd2fa810bad51b71",
            "database": "dy_jl_logistics",
            "port": 13306,
            "charset": "utf8mb4",
            "maxconnections": 20,
            "mincached": 2,
            "maxcached": 5,
            "blocking": True,
            "ping": 0,
            "cursorclass": DictCursor
        }
        cls._instance.pool = PooledDB(
            creator=pymysql,
            **pool_config
        )

    @property
    def connection(self):
        """获取带上下文管理的数据库连接"""
        return ConnectionContext(self.pool)

    def get_sync_order_lanshou_list(self):
        """
        获取同步单需要揽收的数据列表
        :return:
        """

        try:
            with DatabaseHandler().connection as cursor:
                start_time = CommonUtil.get_day_before_days(7)
                sql = f"""
                    SELECT
                        id ,
                        mailNo,
                        mailNo_self
                    FROM
                        sync_order_info 
                    WHERE
                        expressLastUpdateTime IS NULL 
                        AND statusStr IS NULL 
                        AND statusCode IS NULL
                        AND mailNo NOT LIKE 'SF%' 
                        AND createTime > '{start_time}'
                """
                cursor.execute(sql)
                result = cursor.fetchall()
                result_list = list(result)
                dbDataList = [LanShouModel(**item) for item in result_list]
                return dbDataList
        except Exception as ex:
            logging.error(f"get_sync_order_lanshou_list异常：{str(ex)}")
            logging.error("get_sync_order_lanshou_list异常:%s", traceback.format_exc())
        finally:
            cursor.close()

    def get_sync_order_update_list(self):
        try:
            with DatabaseHandler().connection as cursor:
                start_time = CommonUtil.get_day_before_days(15)
                sql = f"""
                       SELECT
                            a.id,
                            a.express,
                            a.mailNo,
                            a.mailNo_self,
                            a.hasChangedOrder,
                            a.yuYueShiJian,
                            a.statusCode,
                            a.statusStr,
                            a.pickingCode,
                            a.pickingAddress,
                            a.hasPushPickingCode,
                            a.expressLastUpdateTime,
                            a.kdMobile,
                            b.receiveProvince,
                            b.receiverCity,
                            b.receiverRegion
                        FROM
                            sync_order_info a
                            LEFT JOIN base_order_info b ON a.orderBaseId = b.id 
                        WHERE
                            a.statusStr IS NOT NULL 
                            AND a.statusCode IS NOT NULL
                            AND a.statusCode !='已签收'
                            AND a.statusCode !='已预约'
                            AND a.statusCode !='已提货'
                            AND a.statusCode !='三包派送'
                            AND a.createTime > '{start_time}'
--                             AND a.mailNo_self = '175470635'
                """
                cursor.execute(sql)
                result = cursor.fetchall()
                dbDataList = list(result)
                return dbDataList
        except Exception as ex:
            logging.error(f"get_sync_order_update_list异常：{str(ex)}")
            logging.error("get_sync_order_update_list异常:%s", traceback.format_exc())
        finally:
            cursor.close()

    def get_sync_order_yuyue_list(self):
        """
        获取同步单需要预约的订单列表
        :return:
        """
        try:
            with DatabaseHandler().connection as cursor:
                start_time = CommonUtil.get_day_before_days(15)
                sql = f"""
                       SELECT
                            id,
                            mailNo_self
                        FROM
                            sync_order_info
                        WHERE
                            statusStr IS NOT NULL 
                            AND statusCode IS NOT NULL
                            AND statusCode ='三包派送'
                            AND createTime > '{start_time}'
--                             AND mailNo_self = '153908574'
                """
                cursor.execute(sql)
                result = cursor.fetchall()
                dbDataList = list(result)
                return dbDataList
        except Exception as ex:
            logging.error(f"get_sync_order_yuyue_list异常：{str(ex)}")
            logging.error("get_sync_order_yuyue_list异常:%s", traceback.format_exc())
        finally:
            cursor.close()

    def get_sync_order_tihuo_list(self):
        """
        获取同步单需要提货的订单列表
        :return:
        """
        try:
            with DatabaseHandler().connection as cursor:
                start_time = CommonUtil.get_day_before_days(15)
                sql = f"""
                          SELECT
                               id,
                               mailNo_self
                            FROM
                               sync_order_info
                            WHERE
                               statusStr IS NOT NULL 
                               AND statusCode IS NOT NULL
                               AND statusCode ='已预约'
                               AND createTime > '{start_time}'
                        """
                cursor.execute(sql)
                result = cursor.fetchall()
                dbDataList = list(result)
                return dbDataList
        except Exception as ex:
            logging.error(f"get_sync_order_tihuo_list异常：{str(ex)}")
            logging.error("get_sync_order_tihuo_list异常:%s", traceback.format_exc())
        finally:
            cursor.close()

    def get_sync_order_sign_list(self):
        """
        获取同步单需要签收的订单列表
        :return:
        """
        try:
            with DatabaseHandler().connection as cursor:
                start_time = CommonUtil.get_day_before_days(15)
                sql = f"""
                          SELECT
                               id,
                               express,
                               mailNo,
                               mailNo_self,
                               expressLastUpdateTime
                            FROM
                               sync_order_info
                            WHERE
                               statusStr IS NOT NULL 
                               AND statusCode IS NOT NULL
                               AND (statusCode ='已预约' or statusCode ='已提货')
                               AND createTime > '{start_time}'
                        """
                cursor.execute(sql)
                result = cursor.fetchall()
                dbDataList = list(result)
                return dbDataList
        except Exception as ex:
            logging.error(f"get_sync_order_sign_list异常：{str(ex)}")
            logging.error("get_sync_order_sign_list异常:%s", traceback.format_exc())
        finally:
            cursor.close()

    def update_sync_order_lanshou_info(self, data_list):
        """
        更新同步单物流揽收信息
        :param data_list:
        :return:
        """
        try:
            with DatabaseHandler().connection as cursor:
                updateSql = "UPDATE sync_order_info SET statusCode=%s, statusStr = %s,logisticsInfo = %s,expressLastUpdateTime =%s,updateTime=%s WHERE id = %s"
                values = CommonUtil.get_sync_order_lanshou_values(data_list)
                cursor.executemany(updateSql, values)
        except Exception as ex:
            logging.error(f"update_sync_order_lanshou_info异常：{str(ex)}")
            logging.error("update_sync_order_lanshou_info异常:%s", traceback.format_exc())
        finally:
            cursor.close()

    def update_sync_order_update_info(self, data_list):
        """
        更新同步单物流更新信息
        :param data_list:
        :return:
        """
        try:
            with DatabaseHandler().connection as cursor:
                updateSql = "UPDATE sync_order_info SET statusCode=%s, statusStr = %s,expressLastUpdateTime = %s,logisticsInfo =%s,updateTime =%s WHERE id = %s"
                values = CommonUtil.get_sync_order_update_values(data_list)
                cursor.executemany(updateSql, values)
        except Exception as ex:
            logging.error(f"update_sync_order_update_info异常：{str(ex)}")
            logging.error("update_sync_order_update_info异常:%s", traceback.format_exc())
        finally:
            cursor.close()

    def update_sync_order_yuyue_info(self, data_list):
        """
        更新同步单物流预约信息
        :param data_list:
        :return:
        """
        try:
            with DatabaseHandler().connection as cursor:
                updateSql = "UPDATE sync_order_info SET statusCode=%s, statusStr = %s,logisticsInfo=%s, updateTime =%s WHERE id = %s"
                values = CommonUtil.get_sync_order_yuyue_values(data_list)
                cursor.executemany(updateSql, values)
        except Exception as ex:
            logging.error(f"update_sync_order_yuyue_info异常：{str(ex)}")
            logging.error("update_sync_order_yuyue_info异常:%s", traceback.format_exc())
        finally:
            cursor.close()

    def update_sync_order_tihuo_info(self, data_list):
        """
        更新同步单物流预约信息
        :param data_list:
        :return:
        """
        try:
            with DatabaseHandler().connection as cursor:
                updateSql = "UPDATE sync_order_info SET statusCode=%s, statusStr = %s,logisticsInfo=%s, updateTime =%s WHERE id = %s"
                values = CommonUtil.get_sync_order_tihuo_values(data_list)
                cursor.executemany(updateSql, values)
        except Exception as ex:
            logging.error(f"update_sync_order_yuyue_info异常：{str(ex)}")
            logging.error("update_sync_order_yuyue_info异常:%s", traceback.format_exc())
        finally:
            cursor.close()

    def update_sync_order_sign_info(self, data_list):
        """
        更新同步单物流签收
        :param data_list:
        :return:
        """
        try:
            with DatabaseHandler().connection as cursor:
                updateSql = "UPDATE sync_order_info SET statusCode=%s, statusStr = %s, logisticsInfo = %s, expressLastUpdateTime = %s, updateTime =%s WHERE id = %s"
                values = CommonUtil.get_sync_order_sign_values(data_list)
                cursor.executemany(updateSql, values)
        except Exception as ex:
            logging.error(f"update_sync_order_yuyue_info异常：{str(ex)}")
            logging.error("update_sync_order_yuyue_info异常:%s", traceback.format_exc())
        finally:
            cursor.close()
